package marca;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import util.Conexao;

public class RepositorioMarcaBanco implements IMarca{
	
	Connection con;
	
	@Override
	public void adicionar(Marca marca) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "insert into marca (nome,\"idTipo\")  values (?,?);";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, marca.getNome());
		ps.setInt(2, marca.getTipo().getId());
		ps.execute();
		
	}

	@Override
	public void editar(Marca marca) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "update marca set nome=?, \"idTipo\"=?  where id= ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, marca.getNome());
		ps.setInt(2, marca.getTipo().getId());
		ps.setInt(3, marca.getId());
		ps.execute();
	}

	@Override
	public void remover(int idMarca) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "delete from marca  where id= ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idMarca);
		ps.execute();
	}

	@Override
	public int existe(int idMarca) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		int res = 0;
		String sql = "select * from marca where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idMarca);
		ps.execute();
		if(ps.getResultSet().next()){
			res = 1;
		}
		return res;
	}

	@Override
	public Marca pesquisar(int idMarca) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from marca where id = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idMarca);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		Marca m = null;
		while(rs.next()){
			m = new Marca();
			m.setId(rs.getInt("id"));
			m.setNome(rs.getString("nome"));
			m.getTipo().setId(rs.getInt("idTipo"));
		}
		return m;
	}

	@Override
	public ArrayList<Marca> listar() throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		Statement st = con.createStatement();
		String sql = "Select * from marca;";
		ResultSet rs = st.executeQuery(sql);
		ArrayList<Marca> ms = new ArrayList<Marca>();
		while(rs.next()){
			Marca m = new Marca();
			m.setId(rs.getInt("id"));
			m.setNome(rs.getString("nome"));
			m.getTipo().setId(rs.getInt("idTipo"));
			ms.add(m);
		}
		return  ms;
	}

	@Override
	public ArrayList<Marca> listar(int idTipo) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = "select * from marca where \"idTipo\" = ?;";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, idTipo);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		Marca m = null;
		ArrayList<Marca> ms = new ArrayList<Marca>();
		while(rs.next()){
			m = new Marca();
			m.setId(rs.getInt("id"));
			m.setNome(rs.getString("nome"));
			m.getTipo().setId(rs.getInt("idTipo"));
			ms.add(m);
		}
		return ms;
	}

	@Override
	public ArrayList<Marca> listar(String nome) throws ClassNotFoundException, SQLException {
		con = Conexao.pegarConexao();
		String sql = " select * from marca where lower(nome) like lower(?) || '%';";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, nome);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		ArrayList<Marca> ms = new ArrayList<Marca>();
		while(rs.next()){
			Marca m = new Marca();
			m.setId(rs.getInt("id"));
			m.setNome(rs.getString("nome"));
			m.getTipo().setId(rs.getInt("idTipo"));
			ms.add(m);
		}
		return ms;
	}

}
